package com.sifeng.test;

import com.sifeng.utils.SqlUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.ArrayList;

/**
 * Created by yang_gsh on 2018/11/7.
 */
public class CreateSqlFromExcel {
	private static String excelPath = "E:\\test.xlsx";
	private static String outFilePath = "E:\\token-test.sql";
	private static String sqlSentence = "INSERT INTO TMP(TMP1,TMP2,TMP3) VALUES(?,?,?)";
	private static String content = "";
	public static void createSql(String cExcelPath, String cFilePath, String cSqlSentence) throws IOException {
		excelPath = cExcelPath;
		outFilePath = cFilePath;
		sqlSentence = cSqlSentence;
		createSql();
	}
	public static void createSql() throws IOException {
		InputStream iStream=new FileInputStream(excelPath);
		XSSFWorkbook wb = new XSSFWorkbook(iStream);
		XSSFSheet sheet = wb.getSheetAt(0);
		if (sheet == null) {
			return;
		}

		for (Row row : sheet) {
			if(row == null) {
				continue;
			}
			ArrayList<Object> list = new ArrayList<Object>();
			for (Cell cell : row) {
				if(cell == null) {
					list.add("");
				} else {
					cell.setCellType(Cell.CELL_TYPE_STRING);
					list.add(cell.getStringCellValue());
				}
			}

			content += SqlUtils.formatSentence(sqlSentence, list) + "\n";
		}

		writeContent(content);
		/*FileWriter writer = new FileWriter(outFilePath);
		for (int i = 1; i < wb.getNumberOfSheets(); i++) {
			XSSFSheet sheet = wb.getSheetAt(i);
			if (sheet == null) {
				break;
			}

			for(int j = 1; j <= sheet.getLastRowNum(); j++) {
				System.out.println(String.valueOf(i) + ":" + String.valueOf(j));
				XSSFRow row = sheet.getRow(j);
				if(row == null) {
					continue;
				}
				XSSFCell cell1 = row.getCell(0);
				cell1.setCellType(Cell.CELL_TYPE_STRING);
				String str1 = cell1.getStringCellValue();
				if(str1 == null || str1.trim().equals("")) {
					continue;
				}

				XSSFCell cell2 = row.getCell(1);
				cell2.setCellType(Cell.CELL_TYPE_STRING);
				String str2 = cell2.getStringCellValue();

				XSSFCell cell3 = row.getCell(2);
				cell3.setCellType(Cell.CELL_TYPE_STRING);
				String str3 = cell3.getStringCellValue();

				XSSFCell cell4 = row.getCell(3);
				cell4.setCellType(Cell.CELL_TYPE_STRING);
				String str4 = cell4.getStringCellValue();

				XSSFCell cell5 = row.getCell(4);
				cell5.setCellType(Cell.CELL_TYPE_STRING);
				String str5 = cell5.getStringCellValue();

				String content = "INSERT INTO MKMTMERCPRO (ONE_LEVEL,TWO_LEVEL,BRAND,SHOP_NM,SHOP_ADDRESS,LEVEL_TYP,TM_SMP)\n" +
						"VALUES ('" + str1.trim().replaceAll("\\r|\\n|'|&","") + "','" + str2.trim().replaceAll("\\r|\\n|'|&","")
						+ "','" + str3.trim().replaceAll("\\r|\\n|'|&","") + "','" + str4.trim().replaceAll("\\r|\\n|'|&","")
						+ "','" + str5.trim().replaceAll("\\r|\\n|'|&","") + "','0',TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS'));\n";

				writer.write(content);

			}
		}
		writer.flush();
		writer.close();*/
	}

	public static void writeContent(String content) throws IOException {
		FileWriter writer = new FileWriter(outFilePath);
		writer.write(content);
		writer.flush();
		writer.close();
	}
	public static void main(String[] args) throws IOException {
		CreateSqlFromExcel.createSql();
	}
}
